package com.inspur.controller;

import java.io.File;


import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;



import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.servlet.ModelAndView;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.inspur.constant.Constant;
import com.inspur.pojo.ClientManager;
import com.inspur.pojo.Param;
import com.inspur.pojo.user;
import com.inspur.service.ClientMgrService;
import com.inspur.service.ParamService;
import com.inspur.service.userService;
import com.inspur.util.ImportExcelUtil;

@Controller
public class ClintMgrController {
	@Autowired
	private ClientMgrService clientMgrService;
	@Autowired
	private ParamService paramService;
	@Autowired
	private userService userService;
    
	/**
	 * 客户经理默认查询及条件查询
	 * @param session
	 * @param pn
	 * @param request
	 * @return
	 */
	@RequestMapping(value = "/clientMgrList")
	public String clientMgrList(Model model,
			@RequestParam(value = "pn", defaultValue = "1") Integer pn,
			HttpServletRequest request) {
		PageHelper.startPage(pn, 5);
		List<ClientManager> clientMgrList = null;
		HashMap<String, Object> map = new HashMap<String, Object>();
		// 调用Service层
		String unit = request.getParameter("unit");
		String mgrId = request.getParameter("mgrId");
		String cname = request.getParameter("cname");
		map.put("unit", unit);
		map.put("mgrId", mgrId);
		map.put("cname", cname);

		clientMgrList = clientMgrService.getClientMgrList(map);

		PageInfo<ClientManager> pageInfo = new PageInfo<ClientManager>(
				clientMgrList, clientMgrList.size());

		System.out.println("客户经理的数量：" + clientMgrList.size());
		model.addAttribute("pageInfo", pageInfo);
		model.addAttribute("unit", unit);
		model.addAttribute("mgrId", mgrId);
		model.addAttribute("cname", cname);


		return "/manager/clientMgrList.jsp";
	}
    /**
     * 查询客户经理的详细信息
     * @param mgrId
     * @param model
     * @return
     */
	@RequestMapping(value = "/clientMgrInfo")
	public String clientMgrInfo(String mgrId, Model model) {
		ClientManager clientMgr = clientMgrService.getClientMgrInfo(mgrId);
		model.addAttribute("cmId", mgrId);
		model.addAttribute("mgr", clientMgr);
		return "/manager/clientMgrInfoDetail.jsp";
	}
    /**
     * 客户经理删除
     * @param cmId
     * @return
     */
	@RequestMapping(value = "/deleteMgr")
	public String deleteMgr(String mgrId[]) {
		clientMgrService.deleteMgr(mgrId);
		return "redirect:/manager/clientMgrList.action";
	}
    /**
     * 跳转到新增页面，为了获取下拉列表数据
     * @param model
     * @return
     */
	@RequestMapping("/toMgrAdd")
	public ModelAndView toMgrAdd(Model model) {
		ModelAndView mv = new ModelAndView();
		// mv.addObject("username","zhangsan");
		mv.setViewName("/manager/clientMgrAdd.jsp");
		return mv;
	}
	/**
	 * 客户经理保存
	 * @param mgr
	 * @param pic
	 * @param req
	 * @param model
	 * @return
	 */
	@RequestMapping("/clientMgrAdd")
	public String clientMgrAdd(ClientManager mgr, MultipartFile pic,
			HttpServletRequest req, Model model) {
		String uploadFile_name = "";
		String newFileName = "";
		try {// 原始⽂件名称
			if (pic != null) {
				uploadFile_name = pic.getOriginalFilename();
				// 新⽂件名称
				if (!"".equals(uploadFile_name))
					newFileName = UUID.randomUUID().toString()
							+ uploadFile_name.substring(uploadFile_name
									.lastIndexOf("."));
			
				// 上传图⽚物理路径
				String url = req.getSession().getServletContext()
						.getRealPath("/images/upload");
				
				//String path = "D:\\cmisUpload\\mgrPhotos";
				
				File uploadfile = new java.io.File(url + "/" + newFileName);
				if (!uploadfile.exists()) {
					uploadfile.mkdirs();
				}
				pic.transferTo(uploadfile);
			}

		} catch (IllegalStateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		mgr.setPhoto(newFileName);
		mgr.setStatus("T");
		// 1.获取当前时间
		Date today = new Date();
		mgr.setModifyDate(today);
		//设置主键
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		String time = format.format(today);
		String mgrIds = time.substring(0, 4)+time.substring(5, 7)+time.substring(8, 10);
		int seqValue = userService.getSeqValue();
		int mgrId =Integer.parseInt(mgrIds+seqValue);
		mgr.setMgrId(mgrId);
		clientMgrService.clientMgrAdd(mgr);
		// 同时初始化一条用户信息
		user user = new user();
		user.setId(mgrId);
		user.setUsername(mgr.getCname());
		user.setBirthDay(format.format(mgr.getBirthday()));
		user.setSex(mgr.getSex());
		user.setPassword("666666");
		user.setQuestion("6位吉祥数字");
		user.setAnswer("666666");
		user.setIsEnable("T");
		user.setRoleId(Constant.USER_ROLEID_MANAGER);
		
		user.setRegisterTime(time);
		user.setLastTime(time);
		userService.addUser(user);

		model.addAttribute("message", "保存成功");
		model.addAttribute("saveFlag", "1");
		model.addAttribute("mgr", mgr);
		return "/manager/clientMgrAdd.jsp";
	}
	
	/**
	 * 导出excel
	 * @param request
	 * @param response
	 * @throws Exception
	 */
	@RequestMapping("/exportExcel")
	@ResponseBody
	public void export(HttpServletRequest request, HttpServletResponse response)
			throws Exception {

		List<HashMap<String, Object>> clientMgrList = null;
		HashMap<String, Object> map = new HashMap<String, Object>();
		// 调用Service层
		String unit = request.getParameter("unit");
		String mgrIds = request.getParameter("mgrId");
		String cname = request.getParameter("cname");
		
		map.put("unit", unit);
		map.put("mgrId", mgrIds);
		map.put("cname", cname);

		clientMgrList = clientMgrService.getClientMgrListForExp(map);

		String[] title = { "客户经理编号", "姓名", "性别", "身份证号", "出生日期", "年龄", "民族",
				"政治面貌", "籍贯", "学历", "学位", "毕业院校", "参加工作时间", "入职时间", "办公电话",
				"移动电话", "客户经理等级", "机构", "部门" };// 设置EXCEL的第一行的标题头（改）
		// 创建excel工作薄
		XSSFWorkbook workbook = new XSSFWorkbook();
		// 创建一个工作表sheet
		XSSFSheet sheet = workbook.createSheet();
		// 创建第一行
		XSSFRow row = sheet.createRow(0);
		XSSFCell cell = null;

		// 插入第一行数据 id 地区名称
		for (int i = 0; i < title.length; i++) {

			cell = row.createCell(i);
			// 赋值
			cell.setCellValue(title[i]);
		}
		// 追加数据行数
		int j = 1;
		HashMap<String, Object> list = null;
		for (int i = 0; i < clientMgrList.size(); i++) {
			list = clientMgrList.get(i);
			XSSFRow nextrow = sheet.createRow(i + 1);
			XSSFCell cessk = nextrow.createCell(0);
	
	//		if (mgrId != null)			
			BigDecimal mgrId = (BigDecimal) (list.get("MGRID"));
			if (mgrId != null)		
			cessk.setCellValue(mgrId.intValue());// 客户经理编号
			
			cessk = nextrow.createCell(1);
			cessk.setCellValue((String) list.get("CNAME"));// 姓名
			cessk = nextrow.createCell(2);
			cessk.setCellValue(((String) list.get("SEX")) == "2" ? "女" : "男");// 性别
			cessk = nextrow.createCell(3);
			cessk.setCellValue((String) list.get("CARDNO"));// 身份证号码
			cessk = nextrow.createCell(4);
			DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
			Timestamp birthday = (Timestamp) list.get("BIRTHDAY");
			if (birthday != null)
				cessk.setCellValue(format.format(birthday));// 出生日期
			else
				cessk.setCellValue("");// 出生日期

			cessk = nextrow.createCell(5);
			BigDecimal age = (BigDecimal) (list.get("AGE"));
			if (age != null)
			cessk.setCellValue(age.intValue());// 年龄
			else
				cessk.setCellValue("");
			cessk = nextrow.createCell(6);
			String nation = (String) list.get("NATION");
			Map paramMap = new HashMap<String, String>();
			paramMap.put("paramType", Constant.PARAM_TYPE_NATION);
			paramMap.put("paramCode", nation);
			String nationName = paramService.getParamName(paramMap);
			cessk.setCellValue(nationName);// 民族

			cessk = nextrow.createCell(7);
			String policeStatus = (String) list.get("POLICESTATUS");// 政治面貌
			paramMap.clear();
			paramMap.put("paramType", Constant.PARAM_TYPE_POLICSTATUS);
			paramMap.put("paramCode", policeStatus);
			String policeStatusName = paramService.getParamName(paramMap);
			cessk.setCellValue(policeStatusName);// 民族
			cessk = nextrow.createCell(8);// 籍贯
			cessk.setCellValue((String) list.get("HOMETOWN"));
			cessk = nextrow.createCell(9);// 学历
			String education = (String) list.get("EDUCATION");
			paramMap.clear();
			paramMap.put("paramType", Constant.PARAM_TYPE_EDUCATION);
			paramMap.put("paramCode", education);
			String educationName = paramService.getParamName(paramMap);
			cessk.setCellValue(educationName);

			cessk = nextrow.createCell(10);// 学位
			String degree = (String) list.get("DEGREE");
			paramMap.clear();
			paramMap.put("paramType", Constant.PARAM_TYPE_DEGREE);
			paramMap.put("paramCode", degree);
			String degreeName = paramService.getParamName(paramMap);
			cessk.setCellValue(degreeName);

			cessk = nextrow.createCell(11);// 毕业院校
			cessk.setCellValue((String) list.get("GRADUATION"));

			cessk = nextrow.createCell(12);
			Timestamp hireDate = (Timestamp) list.get("HIREDATE");
			if (hireDate != null)
				cessk.setCellValue(format.format(hireDate));
			else
				cessk.setCellValue("");// 参加工作时间

			cessk = nextrow.createCell(13);
			Timestamp entryDate = (Timestamp) list.get("ENTRYDATE");// 入职时间
			if (entryDate != null)
				cessk.setCellValue(format.format(entryDate));
			else
				cessk.setCellValue("");

			cessk = nextrow.createCell(14);
			cessk.setCellValue((String) list.get("TEL"));

			cessk = nextrow.createCell(15);
			cessk.setCellValue((String) list.get("MOBILE"));

			cessk = nextrow.createCell(16);
			String level = (String) list.get("LEVL");

			String epLevel = "";
			paramMap.clear();
			paramMap.put("paramType", Constant.PARAM_TYPE_MGRLEVEL);
			paramMap.put("paramCode", level);
			epLevel = paramService.getParamName(paramMap);
			cessk.setCellValue(epLevel);// 客户经理级别

			cessk = nextrow.createCell(17);
			cessk.setCellValue((String) list.get("UNIT"));// 机构
			cessk = nextrow.createCell(18);
			cessk.setCellValue((String) list.get("DEPT"));// 部门
			j++;
		}
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Disposition", "attachment;filename="
				+ new String("客户经理信息.xlsx".getBytes(), "iso-8859-1"));
		OutputStream ouputStream;
		try {
			ouputStream = response.getOutputStream();
			workbook.write(ouputStream);
			ouputStream.flush();
			ouputStream.close();
		} catch (IOException e) {
		} finally {
			//workbook.();
		}
	}
	
	/**
	 * 客户经理信息的批量导入
	 * @param request
	 * @param response
	 * @throws Exception
	 */
	@RequestMapping("/uploadExcel")
	public void uploadExcel(HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;

		InputStream in = null;
		List<List<Object>> listob = null;
		MultipartFile file = multipartRequest.getFile("upfile");

		if (file.isEmpty()) {
			throw new Exception("文件不存在！");
		}
		in = file.getInputStream();
		listob = new ImportExcelUtil().getBankListByExcel(in,
				file.getOriginalFilename());
		in.close();
		List<ClientManager> clientList = new ArrayList<ClientManager>();
		List<user> userList = new ArrayList<user>();
		Date today = new Date();
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		String time = format.format(today);
		// 该处可调用service相应方法进行数据保存到数据库中，现只对数据输出
		System.out.println("size==========="+listob.size());
		for (int i = 0; i < listob.size(); i++) {
			List<Object> lo = listob.get(i);
			ClientManager manager = new ClientManager();
			manager.setMgrId(Integer.parseInt(String.valueOf(lo.get(0))));// 客户经理编号
			manager.setCname(String.valueOf(lo.get(1)));// 姓名
			manager.setSex(Integer.parseInt(String.valueOf(lo.get(2))));// 性别
			manager.setCardNo(String.valueOf(lo.get(3)));// 身份证号码
					
			manager.setBirthday(format.parse(String.valueOf(lo.get(4) == null ? "1991-01-01"
					: lo.get(4))));// 出生日期
			manager.setAge(Integer.parseInt(String.valueOf(lo.get(5) == null ? "0"
					: lo.get(5))));// 年龄
			manager.setNation(String.valueOf(lo.get(6)));// 民族
			manager.setPoliceStatus(String.valueOf(lo.get(7)));// 政治面貌
			manager.setHomeTown(String.valueOf(lo.get(8)));// 籍贯
			manager.setEducation(String.valueOf(lo.get(9)));// 学历
			manager.setDegree(String.valueOf(lo.get(10)));// 学位
			manager.setGraduation(String.valueOf(lo.get(11)));// 毕业院校
			manager.setTechnicalTitle(String.valueOf(lo.get(12)));// 专业职称
			manager.setHireDate(format.parse(String.valueOf(lo.get(13) == null ? "1991-01-01"
					: lo.get(13)))); // 参加工作时间
			manager.setEntryDate((format.parse(String
					.valueOf(lo.get(14) == null ? "1991-01-01" : lo.get(14))))); // 入行参加工作时间
			manager.setFinancialYears(Integer.parseInt(String.valueOf(lo
					.get(15) == null ? "0" : lo.get(15))));// 从事金融工作年限
			manager.setWorkYears(Integer.parseInt(String
					.valueOf(lo.get(16) == null ? "0" : lo.get(16))));// 工作年限
			manager.setTel(String.valueOf(lo.get(17)));// 办公电话
			manager.setMobile(String.valueOf(lo.get(18)));// 移动电话
			manager.setUnit(String.valueOf(lo.get(19)));// 机构
			manager.setDept(String.valueOf(lo.get(20)));// 部门
			manager.setLevel(String.valueOf(lo.get(21)));// 客户经理等级

			System.out.println("客户经理信息-->" + manager.toString());

			// clientMgrService.importClientMgr(manager);
			clientList.add(manager);
			user user = new user();
			user.setId(Integer.parseInt(String.valueOf(lo.get(0))));
			user.setUsername(String.valueOf(lo.get(1)));
			user.setSex(Integer.parseInt(String.valueOf(lo.get(2))));
			user.setPassword("666666");
			user.setQuestion("6位吉祥数字");
			user.setAnswer("666666");
			user.setIsEnable("T");
			user.setRoleId(Constant.USER_ROLEID_MANAGER);
			user.setRegisterTime(time);
			user.setLastTime(time);
		    userList.add(user);

		}
		clientMgrService.importClientMgrList(clientList);// 批量插入
		userService.importUser(userList);//

		PrintWriter out = null;
		response.setCharacterEncoding("utf-8"); // 防止ajax接受到的中文信息乱码
		out = response.getWriter();
		out.print("文件导入成功！");
		out.flush();
		out.close();
	}

	@ModelAttribute("nationParam")
	public Map<String, String> getNationList() {
		Map<String, String> nationParam = new HashMap<String, String>();
		List<Param> paramList = paramService
				.getParamList(Constant.PARAM_TYPE_NATION);
		if (paramList != null && paramList.size() > 0) {
			for (int i = 0; i < paramList.size(); i++) {
				Param param = paramList.get(i);
				nationParam.put(param.getParamCode(), param.getParamName());
			}
		}
		return nationParam;
	}

	@ModelAttribute("policeStatus")
	public Map<String, String> getPoliceStatusList() {
		Map<String, String> policeParam = new HashMap<String, String>();
		List<Param> paramList = paramService
				.getParamList(Constant.PARAM_TYPE_POLICSTATUS);
		if (paramList != null && paramList.size() > 0) {
			for (int i = 0; i < paramList.size(); i++) {
				Param param = paramList.get(i);
				policeParam.put(param.getParamCode(), param.getParamName());
			}
		}
		return policeParam;
	}

	@ModelAttribute("education")
	public Map<String, String> getEducationList() {
		Map<String, String> educationParam = new HashMap<String, String>();
		List<Param> paramList = paramService
				.getParamList(Constant.PARAM_TYPE_EDUCATION);
		if (paramList != null && paramList.size() > 0) {
			for (int i = 0; i < paramList.size(); i++) {
				Param param = paramList.get(i);
				educationParam.put(param.getParamCode(), param.getParamName());
			}
		}
		return educationParam;
	}

	@ModelAttribute("degree")
	public Map<String, String> getDegreeList() {
		Map<String, String> degreeParam = new HashMap<String, String>();
		List<Param> paramList = paramService
				.getParamList(Constant.PARAM_TYPE_DEGREE);
		if (paramList != null && paramList.size() > 0) {
			for (int i = 0; i < paramList.size(); i++) {
				Param param = paramList.get(i);
				degreeParam.put(param.getParamCode(), param.getParamName());
			}
		}
		return degreeParam;
	}

	@ModelAttribute("mgrLevel")
	public Map<String, String> getMgrLevelList() {
		Map<String, String> levelParam = new HashMap<String, String>();
		List<Param> paramList = paramService
				.getParamList(Constant.PARAM_TYPE_MGRLEVEL);
		if (paramList != null && paramList.size() > 0) {
			for (int i = 0; i < paramList.size(); i++) {
				Param param = paramList.get(i);
				levelParam.put(param.getParamCode(), param.getParamName());
			}
		}
		return levelParam;
	}

	
}
